﻿Imports System.Data.OleDb

Public Class RestaurantsDetail
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'only do this if arrived from a different page
        If IsPostBack = False Then
            'To prevent an error, if there is no ID then redirect back to list of restuarants
            If Request.Params("ID") = "" Then Response.Redirect("Restaurantlist.aspx")
            Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionStringRestaurants").ConnectionString)
            'select all fields from Restaurant Comments table where fkID from Query string is correct restaurant
            Dim SqlString As String = "SELECT * FROM RestaurantComments WHERE Restaurant_fkID=@f1"
            Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
            cmd.CommandType = CommandType.Text
            'declare f1 variable to provide query string ID
            cmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
            'as data will be returned, use ExecuteReader not ExecuteNonQuery
            oleDbConn.Open()
            Dim dataReader = cmd.ExecuteReader()
            GridView2.DataSource = dataReader
            GridView2.DataBind()

            'log when a user views a restaurant
            Dim RestaurantsViewedSql As String = "INSERT INTO RestaurantsViewed(Viewer, Restaurant_fkID) VALUES (@f1,@f2)"
            Dim cmd2 As OleDbCommand = New OleDbCommand(RestaurantsViewedSql, oleDbConn)
            cmd2.CommandType = CommandType.Text
            cmd2.Parameters.AddWithValue("@f1", User.Identity.Name)
            cmd2.Parameters.AddWithValue("@f2", Request.Params("ID"))
            cmd2.ExecuteNonQuery()


        End If
    End Sub

    Protected Sub btn_SubmitComment_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_SubmitComment.Click
        Dim myGUID As New System.Guid
        myGUID = Guid.NewGuid()
        Dim newPictureName = myGUID.ToString() + ".jpg"
        Dim newMenuName = myGUID.ToString() + ".pdf"

        If f_Picture.HasFile Then
            f_Picture.SaveAs(Request.MapPath("~/Pictures") & "/" & newPictureName)
        End If

        If f_Menu.HasFile Then
            f_Menu.SaveAs(Request.MapPath("~/Menus") & "/" & newMenuName)
        End If

        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionStringRestaurants").ConnectionString)
        Dim SqlString As String = "INSERT INTO RestaurantComments([Comment_title],[Comment],[Gluten-free_rating],[Food_quality],[Price],[Overall_rating],[Photo],[Menu],[Restaurant_fkID],[UserName]) VALUES (@f1,@f2,@f3,@f4,@f5,@f6,@f7,@f8,@f9,@f10)"
        Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", tb_CommentTitle.Text)
        cmd.Parameters.AddWithValue("@f2", tb_Comment.Text)
        cmd.Parameters.AddWithValue("@f3", rbl_GFRating.Text)
        cmd.Parameters.AddWithValue("@f4", rbl_FoodQuality.SelectedValue)
        cmd.Parameters.AddWithValue("@f5", rbl_PriceBracket.SelectedValue)
        cmd.Parameters.AddWithValue("@f6", rbl_Rating.SelectedValue)
        cmd.Parameters.AddWithValue("@f7", "~/Pictures/" & newPictureName)
        cmd.Parameters.AddWithValue("@f8", "~/Menus/" & newMenuName)
        cmd.Parameters.AddWithValue("@f9", Request.Params("ID"))
        cmd.Parameters.AddWithValue("@f10", User.Identity.Name)
        oleDbConn.Open()
        cmd.ExecuteNonQuery()

        'fill gridview2 if isPostBack is true i.e. if the comment has already been added.
        Dim fillComments As String = "SELECT * FROM RestaurantComments WHERE Restaurant_fkID=@f1"
        Dim fillCommentsCmd As OleDbCommand = New OleDbCommand(fillComments, oleDbConn)
        fillCommentsCmd.CommandType = CommandType.Text
        fillCommentsCmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
        Dim dataReader = fillCommentsCmd.ExecuteReader()
        GridView2.DataSource = dataReader
        GridView2.DataBind()
        'this will show the last added comment in the list of comments.
        tb_Comment.Text = ""
        tb_CommentTitle.Text = ""
        rbl_FoodQuality.SelectedValue = ""
        rbl_GFRating.SelectedValue = ""
        rbl_PriceBracket.SelectedValue = ""
        rbl_Rating.SelectedValue = ""
        
    End Sub


    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionStringRestaurants").ConnectionString)
        Dim SqlString As String = "INSERT INTO RestaurantTagJunction(Restaurant_fkID,Tag_fkID) VALUES (@f1,@f2) "
        oleDbConn.Open()
        'loop through to see which tag or tags are selected
        For Each cbl As ListItem In cbl_restauranttags.Items
            If cbl.Selected Then
                'add join to the tags and restaurant table
                Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
                cmd.CommandType = CommandType.Text
                cmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
                cmd.Parameters.AddWithValue("@f2", cbl.Value)
                cmd.ExecuteNonQuery()
                'once value added to db, tick is removed from page.
                cbl.Selected = False
            End If
        Next
        'If list of new tags is empty then ignore the rest of the function.
        If tb_tags.Text = "" Then Return

        'split new tags textbox and break into an array
        Dim newTags = tb_tags.Text.Split(",")
        ' newTags is temporary array, newTag is temporary variable
        For Each newTag In newTags
            'add to the table of tags
            Dim newTagSql As String = "INSERT into Tags(Tag) VALUES(@f1)"
            Dim cmd As OleDbCommand = New OleDbCommand(newTagSql, oleDbConn)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", newTag)
            cmd.ExecuteNonQuery()

            'find the id of the new tag in the table
            'select the top result of the table when the ID is sorted descending (i.e. the highest number will be at the top)
            Dim getLastIDComd As OleDbCommand = New OleDbCommand("SELECT TOP 1 ID from Tags ORDER BY ID DESC", oleDbConn)
            Dim dataReader2 = getLastIDComd.ExecuteReader()
            dataReader2.Read()
            Dim lastID = dataReader2.GetValue(0)

            'link the new tag ID with the restaurantID in the junction table
            Dim cmd1 As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
            cmd1.CommandType = CommandType.Text
            cmd1.Parameters.AddWithValue("@f1", Request.Params("ID"))
            cmd1.Parameters.AddWithValue("@f2", lastID)
            cmd1.ExecuteNonQuery()

            'clear the Textbox with new tags in it
            tb_tags.Text = ""
        Next

    End Sub






End Class